package com.atguigu.medicalsugar.mapper;

import com.atguigu.medicalsugar.bean.TradeGmvBean;
import com.atguigu.medicalsugar.bean.TradeHostpitalBean;
import com.atguigu.medicalsugar.bean.TradePatientAgeRangeBean;
import com.atguigu.medicalsugar.bean.TradePatientSexBean;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TradeMapper {
    @Select("SELECT\n" +
        "    'consultation' AS name,\n" +
        "    sum(consultation_amount) AS amount\n" +
        "FROM dws_trade_doctor_consultation_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "UNION ALL\n" +
        "SELECT\n" +
        "    'consultation_pay' AS name,\n" +
        "    sum(consultation_pay_amount) AS amount\n" +
        "FROM dws_trade_doctor_consultation_pay_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "UNION ALL\n" +
        "SELECT\n" +
        "    'prescription' AS name,\n" +
        "    sum(split_prescription_amount) AS amount\n" +
        "FROM dws_trade_doctor_prescription_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "UNION ALL\n" +
        "SELECT\n" +
        "    'prescription_pay' AS name,\n" +
        "    sum(split_prescription_pay_amount) AS amount\n" +
        "FROM dws_trade_doctor_prescription_pay_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}")
    List<TradeGmvBean> gmv(Integer date);
    
    @Select("SELECT\n" +
        "    patient_sex,\n" +
        "    sum(split_prescription_pay_amount) AS amount\n" +
        "FROM dws_trade_patient_prescription_pay_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY patient_sex")
    List<TradePatientSexBean> prescriptionAmountByPatientSex(Integer date);
    
    @Select("SELECT\n" +
        "    c.age_range age_range,\n" +
        "    c.consultation_amount consultation_amount,\n" +
        "    c_pay.consultation_pay_amount consultation_pay_amount,\n" +
        "    p.prescription_amount prescription_amount,\n" +
        "    p_pay.prescription_pay_amount prescription_pay_amount\n" +
        "FROM\n" +
        "(\n" +
        "    SELECT\n" +
        "        patient_age % 10 AS age_range,\n" +
        "        sum(consultation_amount) AS consultation_amount\n" +
        "    FROM dws_trade_patient_consultation_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY patient_age % 10\n" +
        ") AS c\n" +
        "ALL FULL OUTER JOIN\n" +
        "(\n" +
        "    SELECT\n" +
        "        patient_age % 10 AS age_range,\n" +
        "        sum(consultation_pay_amount) AS consultation_pay_amount\n" +
        "    FROM dws_trade_patient_consultation_pay_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY patient_age % 10\n" +
        ") AS c_pay ON c.age_range = c_pay.age_range\n" +
        "ALL FULL OUTER JOIN\n" +
        "(\n" +
        "    SELECT\n" +
        "        patient_age % 10 AS age_range,\n" +
        "        sum(split_prescription_amount) AS prescription_amount\n" +
        "    FROM dws_trade_patient_prescription_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY patient_age % 10\n" +
        ") AS p ON c.age_range = p.age_range\n" +
        "ALL FULL OUTER JOIN\n" +
        "(\n" +
        "    SELECT\n" +
        "        patient_age % 10 AS age_range,\n" +
        "        sum(split_prescription_pay_amount) AS prescription_pay_amount\n" +
        "    FROM dws_trade_patient_prescription_pay_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY patient_age % 10\n" +
        ") AS p_pay ON c.age_range = p_pay.age_range")
    List<TradePatientAgeRangeBean> amountByPatientAgeRange(Integer date);
    
    @Select("SELECT\n" +
        "    c.hospital_name hospital,\n" +
        "    c.consultation_amount consultation_amount,\n" +
        "    c.consultation_ct consultation_ct,\n" +
        "    c_pay.consultation_pay_amount consultation_pay_amount,\n" +
        "    c_pay.consultation_pay_ct consultation_pay_ct,\n" +
        "    p.prescription_amount prescription_amount,\n" +
        "    p.prescription_ct prescription_ct,\n" +
        "    p_pay.prescription_pay_amount prescription_pay_amount,\n" +
        "    p_pay.prescription_pay_ct prescription_pay_ct\n" +
        "FROM\n" +
        "(\n" +
        "    SELECT\n" +
        "        hospital_name,\n" +
        "        sum(consultation_amount) AS consultation_amount,\n" +
        "        sum(consultation_ct) AS consultation_ct\n" +
        "    FROM dws_trade_doctor_consultation_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY hospital_name\n" +
        ") AS c\n" +
        "ALL FULL OUTER JOIN\n" +
        "(\n" +
        "    SELECT\n" +
        "        hospital_name,\n" +
        "        sum(consultation_pay_amount) AS consultation_pay_amount,\n" +
        "        sum(consultation_pay_ct) AS consultation_pay_ct\n" +
        "    FROM dws_trade_doctor_consultation_pay_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY hospital_name\n" +
        ") AS c_pay ON c.hospital_name = c_pay.hospital_name\n" +
        "ALL FULL OUTER JOIN\n" +
        "(\n" +
        "    SELECT\n" +
        "        hospital_name,\n" +
        "        sum(split_prescription_amount) AS prescription_amount,\n" +
        "        sum(prescription_ct) AS prescription_ct\n" +
        "    FROM dws_trade_doctor_prescription_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY hospital_name\n" +
        ") AS p ON c.hospital_name = p.hospital_name\n" +
        "ALL FULL OUTER JOIN\n" +
        "(\n" +
        "    SELECT\n" +
        "        hospital_name,\n" +
        "        sum(split_prescription_pay_amount) AS prescription_pay_amount,\n" +
        "        sum(prescription_pay_ct) AS prescription_pay_ct\n" +
        "    FROM dws_trade_doctor_prescription_pay_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    GROUP BY hospital_name\n" +
        ") AS p_pay ON c.hospital_name = p_pay.hospital_name")
    List<TradeHostpitalBean> statsByHostpital(Integer date);
}
